  /**
    *  EXEC SP_LOAD_BOM_LOTS 361, 53, 2
  */

  ALTER PROCEDURE SP_LOAD_BOM_LOTS ( @ASSEMBLY_RELEASE_ID INT, @ASSEMBLY_PROCESS_STEP_ID INT, @TO_VENDOR_ID INT )
  AS
  BEGIN
    DECLARE @LOT_ID INT
    DECLARE @LOT_NUMBER VARCHAR(50)
    DECLARE @WAFER_ID INT
    DECLARE @AVAILABLE_QUANTITY INT
    DECLARE @PULLED_QUANTITY INT
    DECLARE @BOM_PROCESS_STEP_ID INT
	DECLARE @BOM_QTY_PER_DIE INT
    DECLARE @IS_SELECTED INT	-- 0 MEANS SELECTED LOT.
    DECLARE @BOM_PART_NUMBER VARCHAR(50)
	DECLARE @VENDOR_NAME	 VARCHAR(50)
	DECLARE @STATUS			 VARCHAR(20)
	DECLARE @IS_BOM_INV_PULLING_BY_WAFER_ID VARCHAR(100)
	DECLARE @CONFIGURED_BOM_PART_NUMBER VARCHAR(50)
    DECLARE @LOT_CURSOR CURSOR
	DECLARE @BOM_PARTS_CURSOR CURSOR
    DECLARE @RESULTS TABLE ( IS_SELECTED INT, LOT_ID INT, LOT_NUMBER VARCHAR(50), WAFER_ID INT, AVAILABLE_QTY INT, PULLED_QTY INT, BOM_PART_NUMBER VARCHAR(50), VENDOR_NAME VARCHAR(50), BOM_QTY_PER_DIE INT, BOM_PROCESS_STEP_ID INT, CONFIGURED_BOM_PART_NUMBER VARCHAR(50) )

    SET @AVAILABLE_QUANTITY = 0
    SET @PULLED_QUANTITY = 0

    --SELECT @BOM_PROCESS_STEP_ID = BOM_PROCESSSTEP_ID FROM ASSEMBLY_BOM_SETUP_TABLE WHERE ASSEMBLY_PROCESSSTEP_ID = @ASSEMBLY_PROCESS_STEP_ID
	
	SET @IS_BOM_INV_PULLING_BY_WAFER_ID = 'FALSE'
	SELECT @IS_BOM_INV_PULLING_BY_WAFER_ID = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'SCALAR.CHIPTRACK.OPERATIONS.RELEASE.ISBOMPULLINGBYWAFERID'

	SET @BOM_PARTS_CURSOR = CURSOR FOR SELECT BOM_PROCESSSTEP_ID, QTY FROM ASSEMBLY_BOM_SETUP_TABLE WHERE ASSEMBLY_PROCESSSTEP_ID = @ASSEMBLY_PROCESS_STEP_ID
	OPEN @BOM_PARTS_CURSOR
	FETCH NEXT FROM @BOM_PARTS_CURSOR INTO @BOM_PROCESS_STEP_ID, @BOM_QTY_PER_DIE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @CONFIGURED_BOM_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE ProcessStep_ID = @BOM_PROCESS_STEP_ID
		--FOR GETTING THE LOTS AVAILABLE FOR THE BOM PART NUMBER
		IF ( @IS_BOM_INV_PULLING_BY_WAFER_ID = 'TRUE' )
		BEGIN
			SET @LOT_CURSOR = CURSOR FOR SELECT SBRT.LOT_ID, LOT_NUMBER, WAFER_ID, PART_NUMBER, L_VENDOR_NAME, BIN_1_BALANCE_QTY FROM SORT_BIN_RELEASE_TABLE SBRT, LOT_TABLE LT, PRODUCT P, LOCATION L 
												WHERE SBRT.LOT_ID = LT.LOT_ID AND LT.LOCATION_ID = L.LOCATION_ID AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = @BOM_PROCESS_STEP_ID 
												AND LT.LOCATION_ID = (select LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @TO_VENDOR_ID AND L_TYPE = 'Die Bank') AND Lt.Lot_Current_Status = 'Running'
												UNION 
												SELECT SBRT.LOT_ID, LOT_NUMBER, WAFER_ID, PART_NUMBER, L_VENDOR_NAME, BIN_1_BALANCE_QTY FROM 
												SORT_BIN_RELEASE_TABLE SBRT, LOT_TABLE LT, PRODUCT P, LOCATION L 
												WHERE SBRT.LOT_ID = LT.LOT_ID AND LT.LOCATION_ID = L.LOCATION_ID 
												AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID IN (SELECT Substitute_Product_ProcessStep_ID FROM Product_Substitutables_Table WHERE Product_ProcessStep_ID = @BOM_PROCESS_STEP_ID )
												AND LT.LOCATION_ID = (select LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @TO_VENDOR_ID AND L_TYPE = 'Die Bank') 
												AND Lt.Lot_Current_Status = 'Running'  ORDER BY SBRT.LOT_ID
			OPEN @LOT_CURSOR
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @LOT_NUMBER, @WAFER_ID, @BOM_PART_NUMBER, @VENDOR_NAME, @AVAILABLE_QUANTITY
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @PULLED_QUANTITY = 0
				SET @IS_SELECTED = 1
				--FOR GETTING THE QUANTITY AVAILABLE FOR RELEASE
				--FOR GETTING THE PULLED QUANTITY.
				IF ( @ASSEMBLY_RELEASE_ID IS NOT NULL AND @ASSEMBLY_RELEASE_ID > 0 )
				BEGIN
					SELECT @STATUS = [STATUS] FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID
					SELECT @PULLED_QUANTITY = QTY_PULLED FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
					--ADDED QUANTITY PULLED TO QUANTITY AVAILABLE
					IF ( @PULLED_QUANTITY IS NOT NULL AND @PULLED_QUANTITY > 0 )
					BEGIN
						IF @STATUS = 'CANCELLED'
						BEGIN
							SET @AVAILABLE_QUANTITY = ISNULL(@AVAILABLE_QUANTITY,0)	
						END
						ELSE
						BEGIN
							SET @AVAILABLE_QUANTITY = ISNULL(@AVAILABLE_QUANTITY,0) + @PULLED_QUANTITY
						END
						SET @IS_SELECTED = 0
					END
				END
				IF ( ( @AVAILABLE_QUANTITY IS NOT NULL AND @AVAILABLE_QUANTITY > 0 ) )
				BEGIN
					INSERT INTO @RESULTS VALUES ( @IS_SELECTED, @LOT_ID, @LOT_NUMBER, @WAFER_ID, @AVAILABLE_QUANTITY, @PULLED_QUANTITY, @BOM_PART_NUMBER,@VENDOR_NAME, @BOM_QTY_PER_DIE, @BOM_PROCESS_STEP_ID, @CONFIGURED_BOM_PART_NUMBER )
				END
				SET @AVAILABLE_QUANTITY = 0
				SET @LOT_ID = NULL
				SET @LOT_NUMBER = NULL
				SET @WAFER_ID = NULL
				SET @STATUS = NULL
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @LOT_NUMBER, @WAFER_ID, @BOM_PART_NUMBER, @VENDOR_NAME, @AVAILABLE_QUANTITY
			END

			INSERT INTO @RESULTS SELECT 0,LT.LOT_ID, LOT_NUMBER, WAFER_ID, QTY_PULLED, QTY_PULLED,PART_NUMBER, L_VENDOR_NAME, ISNULL((SELECT QTY FROM ASSEMBLY_BOM_SETUP_TABLE WHERE ASSEMBLY_PROCESSSTEP_ID = @ASSEMBLY_PROCESS_STEP_ID AND BOM_PROCESSSTEP_ID = LT.PROCESSSTEP_ID),1), LT.PROCESSSTEP_ID, @CONFIGURED_BOM_PART_NUMBER FROM BOM_RELEASE_LOT_TABLE BRLT, LOT_TABLE LT, PRODUCT P, LOCATION L 
											WHERE BRLT.LOT_ID = LT.LOT_ID AND LT.LOCATION_ID = L.LOCATION_ID AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID 
											AND ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_CURRENT_STATUS = 'Close'
		END
		ELSE --BOM PULLING WITHOUT WAFER ID
		BEGIN
			SET @LOT_CURSOR = CURSOR FOR SELECT LT.LOT_ID, LOT_NUMBER, ISNULL( I.IL_QTY_BALANCE, QTY_IN ), PART_NUMBER, L_VENDOR_NAME FROM LOT_TABLE LT, INVENTORY_LOT I, LOCATION L, PRODUCT P WHERE  LT.LOT_ID = I.LOT_ID AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID AND LT.PROCESSSTEP_ID = @BOM_PROCESS_STEP_ID 
										  UNION	
										  SELECT LT.LOT_ID, LOT_NUMBER, ISNULL( I.IL_QTY_BALANCE, QTY_IN ), PART_NUMBER, L_VENDOR_NAME FROM LOT_TABLE LT, INVENTORY_LOT I, LOCATION L, PRODUCT P WHERE  LT.LOT_ID = I.LOT_ID AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID AND LT.PROCESSSTEP_ID IN ( (SELECT Substitute_Product_ProcessStep_ID FROM Product_Substitutables_Table WHERE Product_ProcessStep_ID = @BOM_PROCESS_STEP_ID ) )
			OPEN @LOT_CURSOR
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @LOT_NUMBER, @AVAILABLE_QUANTITY, @BOM_PART_NUMBER,@VENDOR_NAME	
			WHILE @@FETCH_STATUS = 0
			BEGIN	
				IF ( @LOT_ID <> -1 AND @LOT_ID <> 0 )
				BEGIN
					--SET @AVAILABLE_QUANTITY = 0
					SET @PULLED_QUANTITY = 0
					SET @IS_SELECTED = 1

					--FOR GETTING THE PULLED QUANTITY.
					IF ( @ASSEMBLY_RELEASE_ID IS NOT NULL AND @ASSEMBLY_RELEASE_ID > 0 )
					BEGIN
						SELECT @PULLED_QUANTITY = ISNULL( QTY_PULLED, 0 ) FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID
						IF ( @PULLED_QUANTITY IS NOT NULL AND @PULLED_QUANTITY > 0 ) --ADDED QUANTITY PULLED TO QUANTITY AVAILABLE
						BEGIN
							SET @AVAILABLE_QUANTITY = ISNULL( @AVAILABLE_QUANTITY, 0 ) + @PULLED_QUANTITY
							SET @IS_SELECTED = 0
						END
					END

					--PRINT ' CHECK ISNULL @AVAILABLE_QUANTITY =  ' + CONVERT(VARCHAR, ISNULL( @AVAILABLE_QUANTITY, '-111' ) )

					IF ( ( @AVAILABLE_QUANTITY IS NOT NULL AND @AVAILABLE_QUANTITY > 0 ) )
					BEGIN
						INSERT INTO @RESULTS(IS_SELECTED, LOT_ID, LOT_NUMBER, AVAILABLE_QTY, PULLED_QTY, BOM_PART_NUMBER, VENDOR_NAME, BOM_QTY_PER_DIE, BOM_PROCESS_STEP_ID, CONFIGURED_BOM_PART_NUMBER) VALUES ( @IS_SELECTED, @LOT_ID, @LOT_NUMBER, @AVAILABLE_QUANTITY, @PULLED_QUANTITY, @BOM_PART_NUMBER, @VENDOR_NAME, @BOM_QTY_PER_DIE, @BOM_PROCESS_STEP_ID, @CONFIGURED_BOM_PART_NUMBER )
					END
				END
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @LOT_NUMBER, @AVAILABLE_QUANTITY, @BOM_PART_NUMBER,@VENDOR_NAME				
			END
		END

		CLOSE @LOT_CURSOR
		DEALLOCATE @LOT_CURSOR
		FETCH NEXT FROM @BOM_PARTS_CURSOR INTO @BOM_PROCESS_STEP_ID, @BOM_QTY_PER_DIE
	END
	CLOSE @BOM_PARTS_CURSOR
	DEALLOCATE @BOM_PARTS_CURSOR

    SELECT * FROM @RESULTS ORDER BY BOM_PART_NUMBER, LOT_ID, WAFER_ID
  END